package com.javaweb.admin.service;

import com.javaweb.admin.entity.ConverRate;
import com.javaweb.common.config.CommonConfig;
import com.javaweb.common.utils.DBUtils;
import com.javaweb.common.utils.SQLUtils;
import com.javaweb.common.utils.StringUtils;
import com.javaweb.system.utils.ShiroUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

public class SQLDBQuery {

    public static boolean GetNC63SalesOrderByExternaOrderIDAndMedicCodeIsExistYs(String DDMXBH)
    {
        try
        {

            String sql = "select vbillcode from "+ CommonConfig.ncSaleOrder +" where  vbdef8 =  '"+DDMXBH+"' and  dr =0";
            ResultSet rs = DBUtils.GetResultSet(sql);
            if(rs==null)
            {
                return false;
            }
            return  rs.next();

        }
        catch(Exception ex)
        {
            return  false;
        }


    }

    public static String getNC63CustAddrPk(String custcode, String custAddrName)
    {
        String rtnVal = "";
        try
        {
            String sql = "select pk_address, detailinfo, custcode, custname from "+CommonConfig.ncCustAddr+" where  ( email <> '@' or email is null ) and nvl(ADDR_DR,0) =0 and nvl(custaddr_dr,0) =0   and  custcode = '"+custcode+"' and detailinfo = '"+custAddrName+"'";
            List<HashMap> rs = DBUtils.convertList(DBUtils.GetResultSet(sql));
            if(rs.size()>0)
            {
                rtnVal = rs.get(0).get("pk_address").toString();
            }

        }
        catch(Exception ex)
        {
            rtnVal = "";
        }
        return rtnVal;
    }

    public static List<HashMap> getCustInfoByYYBM(String yybm, String psdbm) throws SQLException {
        String sql = "  SELECT TOP 1 Y_YgptAddrContrast.YYBM  , Y_YgptAddrContrast.YYMC,isnull(Y_YgptAddrContrast.erpwarehouse,'') erpwarehouse,NC_custinfodoc.custname ,Y_YgptAddrContrast.custaddrname,   NC_custinfodoc.custcode,   " +
                " (case when NC_custinfodoc.billCustcode is null or Rtrim(NC_custinfodoc.billCustcode) = '' then NC_custinfodoc.custcode else NC_custinfodoc.billCustcode end ) as billCustcode," +
                " (case when NC_custinfodoc.billCustName is null or Rtrim(NC_custinfodoc.billCustName) = '' then NC_custinfodoc.custname else NC_custinfodoc.billCustName end ) as billCustName, " +
                " (case   WHEN Y_YgptAddrContrast.ywy is null   OR Rtrim(Y_YgptAddrContrast.ywy) = '' THEN  NC_custinfodoc.deptcode ELSE  b.deptcode  END ) AS deptcode, " +
                " (case   WHEN Y_YgptAddrContrast.ywy is null   OR Rtrim(Y_YgptAddrContrast.ywy) = '' THEN  NC_custinfodoc.deptname ELSE  b.deptname  END ) AS deptname, " +
                " (case when Y_YgptAddrContrast.ywy is null or Rtrim(Y_YgptAddrContrast.ywy) = '' then NC_custinfodoc.psncode else Y_YgptAddrContrast.ywy end ) as psncode,   NC_custinfodoc.gpoarea, isnull(NC_custinfodoc.fpmemo,'') as fpmemo   " +
                " FROM Y_YgptAddrContrast with(nolock) " +
                " left join NC_custinfodoc with(nolock)  on Y_YgptAddrContrast.custcode = NC_custinfodoc.custcode " +
                " left join ( " +
                "      select   deptcode,deptname, psncode, psnname from NC_custinfodoc   group by  deptcode,deptname, psncode, psnname       " +
                " ) b on Y_YgptAddrContrast.ywy = b.psncode       " +
                " where YYBM = '" + yybm + "' and PSDBM = '" + psdbm + "' order by Y_YgptAddrContrast.ts desc ";
        return SQLUtils.convertList(SQLUtils.GetResultSet(sql));

    }

    public static ConverRate getInvConverRateByZXSPBM(String ZXSPBM, String CFGG) throws SQLException {
        String selSql = "select Y_ConverRate.converRateType,Y_ConverRate.converRate, Y_ConverRate.invcode, Y_ConverRate.invname, " +
                " NC_invinfodoc.measname, NC_invinfodoc.meascode, NC_invinfodoc.taxratcode,NC_invinfodoc.taxratio, Y_ConverRate.invalidFlag, isnull(Y_ConverRate.invalidMemo,'') as invalidMemo,NC_invinfodoc.mandef14   " +
                " from Y_ConverRate  with(nolock) " +
                " left join NC_invinfodoc with(nolock) on  Y_ConverRate.invcode = NC_invinfodoc.invcode " +
                " where ZXSPBM = '"+ZXSPBM+"' and CFGG = '"+CFGG+"'";
        List<HashMap> list = SQLUtils.convertList(SQLUtils.GetResultSet(selSql));
        if(list.size()==0) return null;
        if(StringUtils.isNullOrEmpty(list.get(0).get("invcode"))) return null;
        ConverRate conRate = new ConverRate();
        conRate.setConverRate(list.get(0).get("converRate").toString());
        conRate.setConverRateType(list.get(0).get("converRateType").toString());
        conRate.setInvcode(list.get(0).get("invcode").toString());
        conRate.setInvname(list.get(0).get("invname").toString());
        conRate.setMeascode(list.get(0).get("meascode").toString());
        conRate.setMeasname(list.get(0).get("measname").toString());
        conRate.setTaxratcode(list.get(0).get("taxratcode").toString());
        conRate.setTaxratio(list.get(0).get("taxratio").toString());
        conRate.setInvalidFlag(Boolean.valueOf(list.get(0).get("invalidFlag").toString()));
        conRate.setInvalidMemo(list.get(0).get("invalidMemo").toString());
        conRate.setGoodsType(list.get(0).get("mandef14").toString());//药品分类
        return conRate;


    }


    public static List<HashMap> getNc63PriceByGpo(String custcode,String invcode, String custArea) throws SQLException {
        String sql = "select * from "+CommonConfig.ncPrmTarifflist+" where custcode ='" + custcode + "' and dr=0  and invcode = '"+invcode+"'";
        return DBUtils.convertList(DBUtils.GetResultSet(sql));

    }

    // 获取配送单的记录条数， 过滤折扣品种
    public static String getPsdReco(String psdtm,String csaleid)
    {
        String retVal = "";
        String sql = " select count(Cgoldtaxcode) as psdrec from ( " +
            " select Cgoldtaxcode from Y_YQ004_RS_DETAIL " +
            " inner join D_SosaleinvoiceDetailInfo on Y_YQ004_RS_DETAIL.Cinvoice_bid = D_SosaleinvoiceDetailInfo.Cinvoice_bid " +
            " inner join D_SosaleinvoiceInfo on D_SosaleinvoiceDetailInfo.Csaleid = D_SosaleinvoiceInfo.Csaleid " +
            " where Cgoldtaxcode like '%"+psdtm+"%' and  D_SosaleinvoiceDetailInfo.Csaleid='"+csaleid+"' and (Y_YQ004_RS_DETAIL.invname not like '%折扣%' or Y_YQ004_RS_DETAIL.invname not like '%折让%') " +
            " group by ZXSPBM, Y_YQ004_RS_DETAIL.DDMXBH,D_SosaleinvoiceDetailInfo.cbatchid ,Cgoldtaxcode" +
            " ) a  ";
        try {
            List<HashMap> list = SQLUtils.convertList(SQLUtils.GetResultSet(sql));
            if(list.size()>0)
            {
                retVal = list.get(0).get("psdrec").toString();
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return retVal;

    }

    public static List<HashMap> GetYQ004DetaiListByCsaleid(String csaleid) throws SQLException {
        String sql = "select * from v_yq004_rs_detail where csaleid ='" + csaleid + "'";
        return SQLUtils.convertList(SQLUtils.GetResultSet(sql));
    }

    public static List<HashMap> GetSaleInvoiceListByCsaleid(String csaleid) throws SQLException {
        String sql = "select * from v_ygpt_saleinvoice where glmxbh ='" + csaleid + "'";
        return SQLUtils.convertList(SQLUtils.GetResultSet(sql));
    }
    public static List<HashMap> GetPsdDetailListByPsdh(String psdh) throws SQLException {
        String sql = "select * from v_ygpt_dispatching_b where psdh ='" + psdh + "'";
        return SQLUtils.convertList(SQLUtils.GetResultSet(sql));
    }

    public static List<HashMap> GetConverRateByZXSPBM(String zxspbm) throws SQLException {
        String sql = "select  * from Y_ConverRate where ZXSPBM='" + zxspbm + "'";
        return SQLUtils.convertList(SQLUtils.GetResultSet(sql));

    }

    public static void setConverRateValue(String converRate, String pk_converrate, String converRateType, String invcode, String invname, int invalidFlag, String invalidMemo) throws SQLException {
        String upSql = String.format("update Y_ConverRate  set converRate = '%s',converRateType='%s',invcode ='%s', invname ='%s', ts = CONVERT(varchar(100), GETDATE(), 20) where  zxspbm =  '"+pk_converrate+"'", converRate,converRateType, invcode, invname, invalidFlag, invalidMemo);
        SQLUtils.ExecNonQuery(upSql);
    }

    public static void setYgCustAddrInfo(String updateId, String custAddr, String ywyCode, String custcode, String custname, String pkCustAddr,String erpwarehouse) throws SQLException {
        String upSql = String.format("update   Y_YgptAddrContrast  set custaddrname = '%s', ywy='%s',  custcode='%s', custname='%s', pkcustaddr='%s',erpwarehouse='%s'    where  id =  '%s'", custAddr, ywyCode, custcode, custname, pkCustAddr, erpwarehouse,updateId);
        SQLUtils.ExecNonQuery(upSql);
    }
    public static void updateYQ004YgPsdbh(String psdbh, String csaleid) throws SQLException {
        String upSql = String.format("update   Y_YQ004_RS  set psdbh = '%s' where  csaleid =  '%s'", psdbh, csaleid);
        SQLUtils.ExecNonQuery(upSql);
    }
    public static void updateYQ010YgPsdbh(String psdbh, String ddmxbh) throws SQLException {
        String upSql = String.format("update   Y_YQ010_RS  set psdbh = '%s' where  ddmxbh =  '%s'", psdbh, ddmxbh);
        SQLUtils.ExecNonQuery(upSql);
    }

    public static void updateYQ004YgPsmxbh(String psdbh, String csaleid,String psdtm,String scph,String xsddh) throws SQLException {
        String upSql = String.format("update   Y_YQ004_RS_DETAIL  set psmxbh = '%s' where  csaleid =  '%s' and psdtm='%s' and scph='%s' and xsddh='%s'", psdbh, csaleid,psdtm,scph,xsddh);
        SQLUtils.ExecNonQuery(upSql);
    }

    public static void updateYQ004YgPsState(String ygpsFlag, String csaleid) throws SQLException {
        String upSql = String.format("update   Y_YQ004_RS  set ygpsUpFlag = '%s' where  csaleid =  '%s'", ygpsFlag, csaleid);
        SQLUtils.ExecNonQuery(upSql);
    }

    public static void updateYQ010YgPsState(String ygpsFlag, String ddmxbh) throws SQLException {
        String upSql = String.format("update   Y_YQ010_RS  set ygpsUpFlag = '%s' where  ddmxbh =  '%s'", ygpsFlag, ddmxbh);
        SQLUtils.ExecNonQuery(upSql);
    }
    public static void updateYQ039YgddXylx(String xylx, String ddmxbh) throws SQLException {
        String upSql = String.format("update   Y_YQ010_RS  set xylx = '%s' where  ddmxbh =  '%s'", xylx, ddmxbh);
        SQLUtils.ExecNonQuery(upSql);
    }

    /*
    订单代填提交成功后，根据返回的订单编号更新
    * */
    public static void updateYQ005YgddDdbh(String replaceno, String ddbh) throws SQLException {
        String upSql = String.format("update   Y_YQ005  set status=1,ddbh = '%s' where  replaceno =  '%s'", ddbh, replaceno);
        SQLUtils.ExecNonQuery(upSql);
    }
    public static void updateYQ005Status(String replaceno,Integer status) throws SQLException {
        String upSql = String.format("update   Y_YQ005  set status="+status.toString()+" where  replaceno =  '%s'", replaceno);
        SQLUtils.ExecNonQuery(upSql);
    }

    public static void updateYQ004YgFpState(String ygpsFlag, String csaleid) throws SQLException {
        String upSql = String.format("update   Y_YQ004_RS  set ygfpUpFlag = '%s' where  csaleid =  '%s'", ygpsFlag, csaleid);
        SQLUtils.ExecNonQuery(upSql);
    }
    public static void updateYQ010YgFpState(String ygpsFlag, String ddmxbh) throws SQLException {
        String upSql = String.format("update   Y_YQ010_RS  set ygfpUpFlag = '%s' where  ddmxbh =  '%s'", ygpsFlag, ddmxbh);
        SQLUtils.ExecNonQuery(upSql);
    }
    public static void updatePruchinvoiceFpState(String status,String fpid,String cwxx, String id) throws SQLException {
        String upSql = "";
        if(!StringUtils.isEmpty(fpid)) {
            upSql = String.format("update   d_poinvoiceinfo  set status = '%s',fpid='%s',retmemo = '%s' where  cinvoiceid =  '%s'", status, fpid, cwxx,id);
        }else{
            upSql = String.format("update   d_poinvoiceinfo  set status = '%s',retmemo = '%s' where  cinvoiceid =  '%s'", status,cwxx, id);
        }
        SQLUtils.ExecNonQuery(upSql);
    }

    public static void updateInvoiceRelationFpState(String status,String fpid, String id) throws SQLException {
        String upSql = "";
        if(!StringUtils.isEmpty(fpid)) {
            upSql = String.format("update   m_invoicesrelation  set status = '%s',glid='%s' where  businessid =  '%s'", status, fpid, id);
        }else{
            upSql = String.format("update   m_invoicesrelation  set status = '%s' where  businessid =  '%s'", status, id);
        }
        SQLUtils.ExecNonQuery(upSql);
    }

    public static void insertOrderTrackDetail(String csaleorderid, String trackType, String trackDetail) throws SQLException {
        String insertSql = "insert into  D_OrderTrackDetail  (csaleorderid, trackType, trackDetail)  " +
                "  values ('" + csaleorderid + "','" + trackType + "','"+ trackDetail + "')";
        SQLUtils.ExecNonQuery(insertSql);
    }

    public static void insertPostResultXml(String[] rtnVal, String postId, String YQTxt) throws SQLException {
        String insertSql = " INSERT INTO D_PostResultXml(PostId ,YQID ,PostXml ,RetPostXml ,ZTCLJG ,RetMemo ,create_date ,create_usraccount)  VALUES ( "
                + "'" + postId + "',"
                + "'" + YQTxt + "', "
                + "'" + rtnVal[1] + "', "
                + "'" + rtnVal[2] + "', "
                + "'" + rtnVal[4] + "', "
                + "'" + rtnVal[5] + "', "
                + "GETDATE(), "
                + "'" + ShiroUtils.getUserAccount() + "') ";
        SQLUtils.ExecNonQuery(insertSql);

    }



}
